import calendar
import datetime
import time
import pandas as pd
from h3yun.service import goods_service, site_data_service
from config.excel_config import month_sales_volume_column_h3yun_code as m_col
from utils.clean_utils import exchange_dict_key_and_value
import re
import tkinter as tk
from tkinter import messagebox
from tkinter import ttk
import shutil
import os
import sys


# 佳妮的需求

# TODO 是否要去除重复流向的数据 比如血气

def handle_daily_data(year=2024, month=5):
    # 1. 先查本月的销售数据流向 # F0000014 产品编码(公司内部的)
    sale_data = goods_service.find_daily_sales_data(matchers=[])
    first_day, last_day = get_search_date_range(datetime.date(year, month, 1))
    # 找出本月的数据
    sale_data = list(filter(lambda s: first_day <= s['F0000001'] <= last_day, sale_data))
    # 只要试剂的数据
    sale_data = list(filter(lambda s: re.match(r'^01\d{5}$', str(s['F0000014'])), sale_data))
    # 先按照闪链的规则清洗掉血气产品
    sale_data = list(filter(lambda s: remove_multiple_flow(s), sale_data))
    # df = pd.DataFrame(sale_data)
    # df.to_excel("sales.xlsx", index=False)
    # 2. 查询医疗器械产品信息 匹配规格等字段  F0000039 产品编码
    medical_device_product = goods_service.find_medical_device_product_information_data(matchers=[])
    medical_device_product_dict = list_to_dict(medical_device_product, "F0000039")
    sales_person_site_dict = get_sales_person_site_dict()

    # 3.1 匹配 销售数据流向 和 医疗器械产品信息表
    CPYJMC, CPYJBM, CPEJMC, CPEJBM, GG, XH, RF_GG, exception_msg = [], [], [], [], [], [], [], []
    # 3.2 匹配销售人员站点信息备案表
    ZY, JL, ZJ, ZJL, SF, CS = [], [], [], [], [], []
    for item in sale_data:
        temp = medical_device_product_dict.get(item['F0000014'], None)
        if temp is not None:
            CPYJMC.append(temp['F0000040'])  # 产品一级名称
            CPYJBM.append(temp['F0000041'])  # 产品一级编码
            CPEJMC.append(temp['F0000043'])  # 产品二级名称
            CPEJBM.append(temp['F0000042'])  # 产品二级编码
            GG.append(temp['F0000035'])  # 规格
            RF_GG.append(temp['F0000044'])  # 人份/单位
            XH.append(temp['F0000015'])  # 型号
            exception_msg.append('')
        else:
            CPYJMC.append('')  # 产品一级名称
            CPYJBM.append('')  # 产品一级编码
            CPEJMC.append('')  # 产品二级名称
            CPEJBM.append('')  # 产品二级编码
            GG.append('')  # 规格
            RF_GG.append('')  # 人份/单位
            XH.append('')  # 型号
            exception_msg.append(f'医疗器械产品信息表没有产品编码{item["F0000014"]}的数据')

        # F0000016 医院编码
        temp = sales_person_site_dict.get(item['F0000016'], None)
        if temp is not None:
            ZY.append(temp['F0000012'])
            JL.append(temp['F0000013'])
            ZJ.append(temp['F0000014'])
            ZJL.append(temp['F0000015'])
            SF.append(temp['F0000021'])
            CS.append(temp['F0000020'])
        else:
            ZY.append('')
            JL.append('')
            ZJ.append('')
            ZJL.append('')
            SF.append('')
            CS.append('')
            exception_msg[len(exception_msg)-1] += f";销售人员站点信息备案表没有站点编码{item['F0000016']}的数据"

    year_month_desc, year_desc, month_desc, season_desc = get_format_date_desc(year, month, len(CPYJMC))
    data = {
        m_col['销售年-月']: year_month_desc,
        m_col['年份']: year_desc,
        m_col['月份']: month_desc,
        m_col['季度']: season_desc,
        m_col['配送商']: list(map(lambda x: x['F0000002'], sale_data)),
        m_col['医院名称']: list(map(lambda x: x['F0000005'], sale_data)),
        m_col['医院编码']: list(map(lambda x: x['F0000016'], sale_data)),
        m_col['产品编码']:  list(map(lambda x: x['F0000014'], sale_data)),
        m_col['产品名称']: list(map(lambda x: x['F0000003'], sale_data)),
        m_col['商品规格']: GG,
        m_col['生产厂家']: list(map(lambda x: x['F0000004'], sale_data)),
        m_col['型号']: XH,
        m_col['产品一级编码']:  CPYJBM,
        m_col['产品一级名称']: CPYJMC,
        m_col['产品二级编码']: CPEJBM,
        m_col['产品二级名称']: CPEJMC,
        m_col['数量']: list(map(lambda x: x['F0000006'], sale_data)),
        #  人份/单位() -> 人份/规格
        m_col['人份/规格']: RF_GG,
        # (人份 / 单位) * 数量
        m_col['数量（按人份）']: ['' for _ in range(len(CPYJMC))],
        m_col['专员']: ZY,
        m_col['经理']: JL,
        m_col['总监']: ZJ,
        m_col['总经理']: ZJL,
        m_col['省份']: SF,
        m_col['城市']: CS
    }
    data = translate_data_key_en_to_zh(data)
    df = pd.DataFrame(data)
    #  df.to_excel("没汇总的数据.xlsx", index=False)

    # sdf.groupby(['部门', '月份'])[['工资', '奖金', '加班费']].agg(['sum', 'mean', 'max']).reset_index()
    summary_column_groupby = []
    # 数量
    summary_column = ['数量']
    for key in m_col.keys():
        if key not in summary_column and key in data.keys():
            summary_column_groupby.append(key)

    summary_df = df.groupby(summary_column_groupby)[summary_column].sum().reset_index()
    summary_df['数量（按人份）'] = summary_df['数量'] * summary_df['人份/规格']

    # 把列名转回编码
    summary_data = {col: summary_df[col].tolist() for col in summary_df.columns}
    summary_data = translate_data_key_zh_to_en(summary_data)
    summary_df = pd.DataFrame(summary_data)

    # 拷贝模板 到当前目录下
    template_file_path = copy_excel_template(month)
    template_df = pd.read_excel(template_file_path)
    template_df = template_df._append(summary_df, ignore_index=True)

    template_df.to_excel(template_file_path, index=False)

    # template_df.to_excel(f"{month}月流向数据（专员分配、分科）.xlsx", index=False)
    messagebox.showinfo("成功", "导出成功")


# 传入案例 today=2025-04-22
def get_search_date_range(today=datetime.date.today()):

    # 获取当月第一天
    first_day = today.replace(day=1)

    # 获取当月最后一天（用 calendar.monthrange 来获取当前月的天数）
    last_day = today.replace(day=calendar.monthrange(today.year, today.month)[1])

    first_day = f"{first_day.year}/{first_day.month}/{first_day.day}"
    last_day = f"{last_day.year}/{last_day.month}/{last_day.day}"

    return first_day, last_day


def list_to_dict(list_data, key):
    data_dict = {}
    for i in list_data:
        data_dict[i[key]] = i
    return data_dict


# 把data里面的key的中文转成英文
def translate_data_key_en_to_zh(data):
    new_m_col = exchange_dict_key_and_value(m_col)
    new_data = {}
    for key, value in data.items():
        new_data[new_m_col[key]] = value
    return new_data


def translate_data_key_zh_to_en(data):
    new_data = {}
    for key, value in data.items():
        new_data[m_col[key]] = value
    return new_data


# 处理 销售年-月 年份 月份 季度
def get_format_date_desc(year, month,  length=0):
    year_month_desc = [f"{year}-{month}" for _ in range(length)]
    year_desc = [f"{year}" for _ in range(length)]
    month_desc = [f"{month}" for _ in range(length)]
    n = [1, 1, 1, 2, 2, 2, 3, 3, 3, 4, 4, 4]
    season_desc = [f"{year}Q{n[month]}" for _ in range(length)]
    return year_month_desc, year_desc, month_desc, season_desc


# F0000004 站点编码
# F0000012 专员
# F0000013 经理
# F0000014 总监
# F0000015 总经理
# F0000021 省份
# F0000020 城市
# F0000010 结束日期
def get_sales_person_site_dict():
    sales_person_site_list = site_data_service.find_sales_person_site_data()
    sales_person_site_dict = {}
    for sales_person_site in sales_person_site_list:
        if sales_person_site_dict.get(sales_person_site["F0000004"], None) is None or len(sales_person_site['F0000010']) == 0:
            sales_person_site_dict[sales_person_site["F0000004"]] = sales_person_site
    return sales_person_site_dict


# 创建主窗口
root = tk.Tk()
root.title("月度流向数据（专员分配、分科）")
root.geometry("300x200")

# 年份选择
tk.Label(root, text="选择年份：").pack(pady=5)
year_values = [str(y) for y in range(datetime.date.today().year-4, datetime.date.today().year+1)]  # 2020 - 2025
year_combobox = ttk.Combobox(root, values=year_values, state='readonly')
year_combobox.current(len(year_values)-1)
year_combobox.pack()

# 月份选择
tk.Label(root, text="选择月份：").pack(pady=5)
month_values = [str(m) for m in range(1, 13)]
month_combobox = ttk.Combobox(root, values=month_values, state='readonly')
curMonth = datetime.date.today().month-2
month_combobox.current(curMonth if curMonth >= 0 else (curMonth+12))
month_combobox.pack()


# 提交按钮
def on_submit():
    year = year_combobox.get()
    month = month_combobox.get()

    if not year or not month:
        messagebox.showerror("错误", "请选择年份和月份")
        return

    handle_daily_data(int(year), int(month))


tk.Button(root, text="提交", command=on_submit).pack(pady=15)


def copy_excel_template(month):
    # 判断是否是 PyInstaller 打包后的运行环境
    if getattr(sys, 'frozen', False):
        base_dir = sys._MEIPASS  # 打包后的临时路径
    else:
        base_dir = os.path.dirname(os.path.abspath(__file__))

    # 模板路径（打包时使用 --add-data template 文件夹）
    src_path = os.path.join(base_dir, "template", "月度流向数据_专员分配_分科_模板.xlsx")

    # 目标路径，放在当前运行目录
    dst_path = os.path.join(os.getcwd(), f"{month}月流向数据（专员分配、分科）_{int(time.time())}.xlsx")

    # 复制文件
    shutil.copy(src_path, dst_path)

    return dst_path


def remove_multiple_flow(s):
    condition = ((s['F0000003'] == '血气测定试剂盒（电极法）Measurement Cartridge')
                 & (s['F0000002'] == '众志飞救医疗科技（深圳）有限公司')
                 & (s['F0000010'] == '众志--闪链配送'))
    return ~condition


if __name__ == '__main__':
    # get_search_date_range()
    # handle_daily_data()
    # year_entry, month_entry = start_gui()
    root.mainloop()
    # handle_daily_data()
    # copy_excel_template(6)
    #get_sales_person_site_dict()
